#!/usr/bin/env python3
import argparse
import os
import sys
from datetime import datetime
from pathlib import Path
from typing import Iterable, List

import pandas as pd
from openpyxl.styles import Alignment, Border, Font, NamedStyle, PatternFill, Side
from openpyxl.utils import get_column_letter


def ensure_directory(path: Path) -> None:
    path.mkdir(parents=True, exist_ok=True)


def list_excel_files(inputs_dir: Path, recursive: bool = False) -> List[Path]:
    patterns = ["*.xlsx", "*.xls"]
    files: List[Path] = []
    if recursive:
        for pattern in patterns:
            files.extend(inputs_dir.rglob(pattern))
    else:
        for pattern in patterns:
            files.extend(inputs_dir.glob(pattern))
    # 过滤临时或锁文件，如 ~$.xlsx
    files = [f for f in files if not f.name.startswith("~$")]
    return sorted(files)


def timestamp_now() -> str:
    # 使用到秒，避免重名
    return datetime.now().strftime("%Y%m%d_%H%M%S")


def read_sheet_as_dataframe(excel_path: Path, sheet_name: str) -> pd.DataFrame:
    try:
        # 默认 header=0，将首行作为列名
        df = pd.read_excel(excel_path, sheet_name=sheet_name, header=0)
        # 将非字符串列名统一为字符串，避免后续写入问题
        df.columns = [str(c) for c in df.columns]
        return df
    except ValueError as e:
        # 常见于空表或无数据时，回退为无表头读取
        try:
            df = pd.read_excel(excel_path, sheet_name=sheet_name, header=None)
            df.columns = [str(c) for c in df.columns]
            return df
        except Exception as inner_e:
            raise inner_e from e


def add_status_column(df: pd.DataFrame) -> pd.DataFrame:
    """为数据框添加'在职状态'列，默认值为'Y'"""
    # 如果数据框为空，创建一个包含'在职状态'列的空数据框
    if df.empty:
        df = pd.DataFrame(columns=['在职状态'])
    else:
        # 添加'在职状态'列，所有行的值设为'Y'
        df['在职状态'] = 'Y'
    return df


# ---------- 样式与列宽 ----------
_thin = Side(style="thin", color="FFBFBFBF")
HEADER_STYLE = NamedStyle(name="header_style")
HEADER_STYLE.font = Font(bold=True, color="FFFFFFFF", name="Calibri", size=11)
HEADER_STYLE.fill = PatternFill("solid", fgColor="FF4472C4")  # 蓝色表头
HEADER_STYLE.alignment = Alignment(horizontal="center", vertical="center")
HEADER_STYLE.border = Border(left=_thin, right=_thin, top=_thin, bottom=_thin)

DATA_STYLE = NamedStyle(name="data_style")
DATA_STYLE.font = Font(bold=False, color="FF000000", name="Calibri", size=11)
DATA_STYLE.alignment = Alignment(horizontal="left", vertical="center", wrap_text=False)
DATA_STYLE.border = Border(left=_thin, right=_thin, top=_thin, bottom=_thin)

ALT_FILL = PatternFill("solid", fgColor="FFF7F7F7")  # 斑马纹浅灰


def _register_style_once(workbook, style: NamedStyle) -> None:
    try:
        workbook.add_named_style(style)
    except ValueError:
        # 已注册则忽略
        pass


def _autofit_columns(worksheet) -> None:
    max_col = worksheet.max_column
    max_row = worksheet.max_row
    for col_idx in range(1, max_col + 1):
        letter = get_column_letter(col_idx)
        max_length = 0
        # 统计表头与数据内容长度（按字符数近似）
        for row_idx in range(1, max_row + 1):
            cell = worksheet.cell(row=row_idx, column=col_idx)
            value = cell.value
            if value is None:
                content_length = 0
            else:
                content_length = len(str(value))
            if content_length > max_length:
                max_length = content_length
        # 适当留白，限制最大宽度
        adjusted_width = min(max_length + 2, 60)
        worksheet.column_dimensions[letter].width = adjusted_width


def _apply_formatting(worksheet, workbook) -> None:
    # 注册样式
    _register_style_once(workbook, HEADER_STYLE)
    _register_style_once(workbook, DATA_STYLE)

    if worksheet.max_column == 0:
        return

    # 冻结首行
    worksheet.freeze_panes = "A2"

    # 设置行高（可选，略增高表头）
    worksheet.row_dimensions[1].height = 22

    # 表头样式
    for col_idx in range(1, worksheet.max_column + 1):
        cell = worksheet.cell(row=1, column=col_idx)
        cell.style = HEADER_STYLE

    # 数据区域样式 + 斑马纹
    for row_idx in range(2, worksheet.max_row + 1):
        is_alt = (row_idx % 2 == 0)
        for col_idx in range(1, worksheet.max_column + 1):
            cell = worksheet.cell(row=row_idx, column=col_idx)
            cell.style = DATA_STYLE
            if is_alt:
                cell.fill = ALT_FILL

    # 自适应列宽
    _autofit_columns(worksheet)


def process_one_excel(source_path: Path, outputs_dir: Path) -> Path:
    original_filename = source_path.stem
    out_name = f"{original_filename}-{timestamp_now()}.xlsx"
    out_path = outputs_dir / out_name

    # 打开 Excel 文件，遍历所有 sheet
    xls = pd.ExcelFile(source_path)
    sheet_names = xls.sheet_names

    with pd.ExcelWriter(out_path, engine="openpyxl") as writer:
        for sheet in sheet_names:
            df = read_sheet_as_dataframe(source_path, sheet)
            # 添加'在职状态'列
            df = add_status_column(df)
            # 控制台输出 header 信息
            headers = list(df.columns)
            print(f"[INFO] 文件: {source_path.name} | Sheet: {sheet} | Header: {headers}")
            # 写回到新的 Excel，保留列名，去掉索引
            df.to_excel(writer, sheet_name=sheet, index=False)

        # 对每个工作表应用格式
        workbook = writer.book
        for sheet in sheet_names:
            ws = writer.sheets[sheet]
            _apply_formatting(ws, workbook)

    return out_path


def run(inputs_dir: Path, outputs_dir: Path, recursive: bool = False) -> None:
    ensure_directory(inputs_dir)
    ensure_directory(outputs_dir)

    files = list_excel_files(inputs_dir, recursive=recursive)
    if not files:
        print(f"[WARN] 未在 {inputs_dir} 发现 Excel 文件 (.xlsx/.xls)")
        return

    print(f"[INFO] 待处理文件数: {len(files)}")
    for idx, f in enumerate(files, start=1):
        try:
            print(f"[INFO] ({idx}/{len(files)}) 处理: {f}")
            out_file = process_one_excel(f, outputs_dir)
            print(f"[OK] 生成: {out_file}")
        except Exception as e:
            print(f"[ERROR] 处理失败: {f} | {e}", file=sys.stderr)


def parse_args(argv: Iterable[str]) -> argparse.Namespace:
    parser = argparse.ArgumentParser(
        description="读取 docs/inputs 下的所有 Excel，并在 docs/outputs 中生成带时间戳的新文件",
    )
    parser.add_argument(
        "--inputs",
        dest="inputs",
        type=str,
        default=str(Path(__file__).resolve().parent.parent / "docs" / "inputs"),
        help="输入目录（默认: 项目根目录/docs/inputs）",
    )
    parser.add_argument(
        "--outputs",
        dest="outputs",
        type=str,
        default=str(Path(__file__).resolve().parent.parent / "docs" / "outputs"),
        help="输出目录（默认: 项目根目录/docs/outputs）",
    )
    parser.add_argument(
        "-r",
        "--recursive",
        dest="recursive",
        action="store_true",
        help="是否递归扫描输入目录",
    )
    return parser.parse_args(list(argv))


if __name__ == "__main__":
    args = parse_args(sys.argv[1:])
    run(inputs_dir=Path(args.inputs), outputs_dir=Path(args.outputs), recursive=args.recursive) 